﻿-- =============================================
-- Author:		<赵洪涛>
-- Edit date:	<2012-05-09>
-- Description:	<项目任务汇总(技术用)>
-- =============================================

CREATE PROCEDURE [dbo].[proc_Project_Task_Count]
	(
		@CompanyId int,
		@DepartmentId int,
		@EmpState int,
		@StartDate nvarchar(50),
		@EndDate nvarchar (50)
	)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepartmentId

	;WITH list2 As(Select UserName,DepartmentId
		,IsNull((Select DName From Department Where Department.Id=e.DepartmentId),'') As Department
		,e.EName
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=1 And IsFinish=0 And CreateDate>=@sRq And CreateDate<=@eRq) As T1_N
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=1 And IsFinish=1 And CreateDate>=@sRq And CreateDate<=@eRq) As T1_Y
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=2 And IsFinish=0 And CreateDate>=@sRq And CreateDate<=@eRq) As T2_N
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=2 And IsFinish=1 And CreateDate>=@sRq And CreateDate<=@eRq) As T2_Y
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=3 And IsFinish=0 And CreateDate>=@sRq And CreateDate<=@eRq) As T3_N
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=3 And IsFinish=1 And CreateDate>=@sRq And CreateDate<=@eRq) As T3_Y
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=4 And IsFinish=0 And CreateDate>=@sRq And CreateDate<=@eRq) As T4_N
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=4 And IsFinish=1 And CreateDate>=@sRq And CreateDate<=@eRq) As T4_Y
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=5 And IsFinish=0 And CreateDate>=@sRq And CreateDate<=@eRq) As T5_N
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=5 And IsFinish=1 And CreateDate>=@sRq And CreateDate<=@eRq) As T5_Y
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=6 And IsFinish=0 And CreateDate>=@sRq And CreateDate<=@eRq) As T6_N
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=6 And IsFinish=1 And CreateDate>=@sRq And CreateDate<=@eRq) As T6_Y
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=7 And IsFinish=0 And CreateDate>=@sRq And CreateDate<=@eRq) As T7_N
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=7 And IsFinish=1 And CreateDate>=@sRq And CreateDate<=@eRq) As T7_Y
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=8 And IsFinish=0 And CreateDate>=@sRq And CreateDate<=@eRq) As T8_N
		,(Select Count(0) From Project_Task Where ExecPerson=e.UserName And WebType=8 And IsFinish=1 And CreateDate>=@sRq And CreateDate<=@eRq) As T8_Y
		,(Case When State<>5 Then 1 Else 2 End) As state
		From Employee e
		Where CompanyId=@CompanyId And DepartmentId In(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T)
		And e.UserName<>''
	),
	list1 As(Select Id,bmbh
	,(case when len(bmbh)>4 then replicate('┆┄┄',(len(bmbh)-4)/2)+isnull(dname,'') else '◆'+isnull(dname,'') end) As DName
	,IsNull((Select Sum(T1_N) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T1_N
	,IsNull((Select Sum(T1_Y) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T1_Y
	,IsNull((Select Sum(T2_N) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T2_N
	,IsNull((Select Sum(T2_Y) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T2_Y
	,IsNull((Select Sum(T3_N) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T3_N
	,IsNull((Select Sum(T3_Y) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T3_Y
	,IsNull((Select Sum(T4_N) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T4_N
	,IsNull((Select Sum(T4_Y) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T4_Y
	,IsNull((Select Sum(T5_N) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T5_N
	,IsNull((Select Sum(T5_Y) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T5_Y
	,IsNull((Select Sum(T6_N) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T6_N
	,IsNull((Select Sum(T6_Y) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T6_Y
	,IsNull((Select Sum(T7_N) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T7_N
	,IsNull((Select Sum(T7_Y) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T7_Y
	,IsNull((Select Sum(T8_N) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T8_N
	,IsNull((Select Sum(T8_Y) From list2 Where Id=departmentid And (@EmpState =0 Or state = @EmpState)),0) As T8_Y
	From Department d
	where CompanyId = @CompanyId And Left(bmbh,Len(@bmbh_T))=@bmbh_T
	),
	List As(Select *
	,(T1_Y+T2_Y+T3_Y+T4_Y+T5_Y+T6_Y+T7_Y+T8_Y) As T9_Y
	,(T1_N+T2_N+T3_N+T4_N+T5_N+T6_N+T7_N+T8_N) As T9_N
	From list1
	)

Select Id,DName,T1_Y,T1_N,T2_Y,T2_N,T3_Y,T3_N,T4_Y,T4_N,T5_Y,T5_N,T6_Y,T6_N,T7_Y,T7_N,T8_Y,T8_N,T9_Y,T9_N	--,bmbh
From List
--Union All
--Select @DepartmentId As Id,'合计' As DName,Sum(T1_Y),Sum(T1_N),Sum(T2_Y),Sum(T2_N)
--,Sum(T3_Y) As T3_Y,Sum(T3_N) As T3_N,Sum(T4_Y) As T4_Y,Sum(T4_N) As T4_N
--,Sum(T5_Y) As T5_Y,Sum(T5_N) As T5_N,Sum(T6_Y) As T6_Y,Sum(T6_N) As T6_N
--,Sum(T7_Y) As T7_Y,Sum(T7_N) As T7_N,Sum(T8_Y) As T8_Y,Sum(T8_N) As T8_N
--,Sum(T9_Y) As T9_Y,Sum(T9_N) As T9_N,'ZZZZ' As bmbh From List
Order By bmbh
End
